IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pAddReturnProductDetail'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pAddReturnProductDetail
GO

CREATE PROC dbo.pAddReturnProductDetail
  @vintReturnProductId     INT,
  @vintProductId		   INT,
  @vintAmount			   INT,
  @vbitSuccess			   BIT OUTPUT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

  DECLARE
    @cstrThisProc              NVARCHAR(128),
    @cintErrGeneral            INT

  SET @cstrThisProc            = N'pAddReturnProductDetail'
  SET @cintErrGeneral          = 50000  -- General error. Used when an
                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/
	DECLARE @intBillDetailId					INT
	DECLARE @intBillDetailAmount				INT
	DECLARE @intInProdDetailId					INT
	DECLARE @intBillDetail_InProdDetailAmount	INT
	DECLARE @intBillDetail_InProdDetailId		INT
	DECLARE @intReturnAmountOnBillDetail		INT
/*_______________________________ MAIN _________________________________*/
	
	
	BEGIN TRANSACTION
	BEGIN TRY	
	INSERT INTO ReturnProductDetail (
		Amount,
		ReturnProductId,
		ProductId
	) VALUES ( 
		@vintAmount,
		@vintReturnProductId,
		@vintProductId
	) 

	-- Increase product amount
	UPDATE Product
	SET Product.Amount = Product.Amount + @vintAmount
	FROM Product
	WHERE Product.Id = @vintProductId	
	
	-- Increase InProdDetail (bottom up) and delete BillDetail_InProdDetail if return amount >= it's amount
	WHILE @vintAmount > 0
	BEGIN
		-- Get latest bill detail to update amount
		SELECT TOP 1
			@intBillDetailId = Id,
			@intBillDetailAmount = Amount
		FROM BillDetail
		WHERE ProductId = @vintProductId
			AND Amount > 0
		ORDER BY Id DESC -- Get latest bill detail
		
		IF @intBillDetailId IS NOT NULL BEGIN
			-- Update amount of bill detail
			UPDATE BillDetail
			SET Amount = CASE 
							WHEN Amount > @vintAmount THEN Amount - @vintAmount
							ELSE 0
						END
			WHERE BillDetail.Id = @intBillDetailId
			
			SET @intReturnAmountOnBillDetail = CASE
												WHEN @vintAmount > @intBillDetailAmount THEN @intBillDetailAmount
												ELSE @vintAmount
											   END
			
			WHILE @intReturnAmountOnBillDetail > 0 BEGIN
				-- Update amout of in prod detail
				SET @intBillDetail_InProdDetailId = NULL
				SET @intBillDetail_InProdDetailAmount = NULL
				SET @intInProdDetailId = NULL
				
				SELECT TOP 1
					@intBillDetail_InProdDetailId = Id,
					@intBillDetail_InProdDetailAmount = Amount,
					@intInProdDetailId = InProdDetailId
				FROM BillDetail_InProdDetail
				WHERE BillDetailId = @intBillDetailId
				ORDER BY Id DESC -- Buttom Up
				
				IF @intBillDetail_InProdDetailId IS NOT NULL BEGIN
					-- Update in prod detail
					UPDATE InOutProdDetail
					SET RemainAmount = CASE
										 WHEN @intReturnAmountOnBillDetail > @intBillDetail_InProdDetailAmount THEN ISNULL(RemainAmount, 0) + @intBillDetail_InProdDetailAmount
										 ELSE ISNULL(RemainAmount, 0) + @intReturnAmountOnBillDetail
									   END
					WHERE InOutProdDetail.Id = @intInProdDetailId
					
					-- Delete BillDetail_InProdDetail if return amount >= it's amount
					IF @intReturnAmountOnBillDetail >= @intBillDetail_InProdDetailAmount BEGIN
						DELETE FROM BillDetail_InProdDetail
						WHERE Id = @intBillDetail_InProdDetailId
					END
					ELSE BEGIN -- Update Amount of BillDetail_InProdDetail if it's amount > return amount
						UPDATE BillDetail_InProdDetail
						SET Amount = Amount - @intReturnAmountOnBillDetail
						WHERE Id = @intBillDetail_InProdDetailId
					END
					
					SET @intReturnAmountOnBillDetail = @intReturnAmountOnBillDetail - @intBillDetail_InProdDetailAmount
					SET @intBillDetail_InProdDetailId = NULL
				END
				ELSE BEGIN
					SET @intReturnAmountOnBillDetail = 0 -- break while
				END
			END -- end while update InOutProdDetail and BillDetail_InProDetail
			
			SET @vintAmount = @vintAmount - @intBillDetailAmount
			SET @intBillDetailId = NULL
		END
		ELSE BEGIN
			SET @vintAmount = 0 -- break while	 	
		END
			
	END -- END WHILE
	
	-- Return value 1 indicates success
	SET @vbitSuccess = 1
	
	END TRY
	BEGIN CATCH
		GOTO lblError
	END CATCH

	IF @@TRANCOUNT > 0 BEGIN
		COMMIT TRANSACTION
	END
	RETURN
/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number
	
	IF @@TRANCOUNT > 0 BEGIN
		ROLLBACK TRANSACTION
	END
	
	SET @vbitSuccess = 0
	
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	
	SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();

	-- Use RAISERROR inside the CATCH block to return error
	-- information about the original error that caused
	-- execution to jump to the CATCH block.
	RAISERROR (@ErrorMessage, -- Message text.
			   @ErrorSeverity, -- Severity.
			   1 -- State.
			   );


